class MssqlSystem {
  constructor(sequelize, dataTypeFormat) {
    this.sequelize = sequelize
    this.dataTypeFormat = dataTypeFormat

    // 测试数据库连接
    this.sequelize.authenticate().then(() => {
      console.log('Connection has been established successfully.')
    }).catch(err => {
      throw new Error('Unable to connect to the database:' + err)
    })
  }

 /**
   * 获取数据库下的表信息
   * @return {Object[]} 数据表信息
   * - {String} TABLE_NAME 数据表名
   * - {String} TABLE_COMMENT 表的注释、备注
   */
  async getDataTables() {
    let sql = ` select A.name TABLE_NAME, ISNULL(B.value, '') TABLE_COMMENT `
            + ` from sys.tables A left join sys.extended_properties B `
            + ` on B.major_id=A.object_id and B.minor_id=0 `

    let dbTables = await this.sequelize.query(sql, {raw: true, type: 'SELECT'})
    return dbTables
  }

  /**
   * 获取数据表下的列信息
   * @return {Object[]} 数据列信息
   * - {String} COLUMN_NAME 列名称
   * - {String} DATA_TYPE 数据类型
   * - {Number} CHARACTER_MAXIMUM_LENGTH  字符最大长度
   * - {String} COLUMN_KEY  关键列[NULL|MUL|PRI]
   * - {String} COLUMN_COMMENT  字段注释、描述
   */
  async getTableColumns(tableName) {
    // 查询表格中的列信息
    let sql = ` select A.name COLUMN_NAME, B.name DATA_TYPE, A.length CHARACTER_MAXIMUM_LENGTH, C.value COLUMN_COMMENT `
            + ` from sys.syscolumns A `
            + ` left join systypes B on A.xtype=B.xtype `
            + ` left join sys.extended_properties C on A.id=C.major_id and A.colid=C.minor_id `
            + ` where A.id=object_id('${tableName}') `
    let tableColumns = await this.sequelize.query(sql, {raw: true, type: 'SELECT'})

    // 查询主键列名
    sql = ` select b.column_name as COLUMN_KEY `
        + ` from information_schema.table_constraints a `
        + ` inner join information_schema.constraint_column_usage b `
        + ` on a.constraint_name = b.constraint_name `
        + ` where a.constraint_type = 'PRIMARY KEY' and a.table_name = '${tableName}' `
    let keyColumns = await this.sequelize.query(sql, {raw: true, type: 'SELECT'})
    let keyColumnsSet = new Set(keyColumns.map(column => column.COLUMN_KEY))

    tableColumns.forEach(column => keyColumnsSet.has(column.COLUMN_NAME) ? column.COLUMN_KEY = true : column.COLUMN_KEY = false)
    tableColumns.forEach(column => column.DATA_TYPE = this.dataTypeFormat.format(column.DATA_TYPE))
    return tableColumns
  }
}

module.exports = MssqlSystem